package com.ls.jdbc;


import com.ls.jdbc.util.JDBCUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;

public class Main {
    public static void main(String[] args) throws Exception {
        Main main = new Main();
//        List<User> all = main.findAll();
//        List<User> all = main.findAll2();
        List<User> all = main.findAll3();
        all.stream().forEach(System.out::println);
    }

    public List<User> findAll3() throws Exception {
        Connection connect = JDBCUtils.getConnection();

        String sql = "select * from  user where id = ? and name = ?";
        PreparedStatement preparedStatement = connect.prepareStatement(sql);
        preparedStatement.setInt(1, 5);
        preparedStatement.setString(2, "丁七");

        ResultSet resultSet = preparedStatement.executeQuery();
        List<User> list = new ArrayList<>();
        User user = null;
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            String card_id = resultSet.getString("card_id");
            user = new User();
            user.setId(id);
            user.setName(name);
            user.setCardId(card_id);
            list.add(user);
        }
        JDBCUtils.close(resultSet, preparedStatement, connect);
        return list;

    }

    public List<User> findAll2() throws Exception {
        Connection connect = JDBCUtils.getConnection();
        Statement statement = connect.createStatement();

        String sql = "select * from  user";
        ResultSet resultSet = statement.executeQuery(sql);
        List<User> list = new ArrayList<>();
        User user = null;
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            String card_id = resultSet.getString("card_id");
            user = new User();
            user.setId(id);
            user.setName(name);
            user.setCardId(card_id);
            list.add(user);
        }
        JDBCUtils.close(resultSet, statement, connect);
        return list;

    }


    public List<User> findAll() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
        Connection connect = DriverManager.getConnection("jdbc:mysql:///test", "root", "123456");
        Statement statement = connect.createStatement();

        String sql = "select * from  user";
        ResultSet resultSet = statement.executeQuery(sql);
        List<User> list = new ArrayList<>();
        User user = null;
        while (resultSet.next()) {
            int id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            String card_id = resultSet.getString("card_id");
            user = new User();
            user.setId(id);
            user.setName(name);
            user.setCardId(card_id);
            list.add(user);
        }
        resultSet.close();
        statement.close();
        connect.close();
        return list;
    }


    public void test() throws Exception {
        Class.forName("com.mysql.jdbc.Driver");
//        Connection connect = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "123456");
        Connection connect = DriverManager.getConnection("jdbc:mysql:///test", "root", "123456");
        Statement statement = connect.createStatement();
        //-------------------
        String sql = "update user set name = '在3' where id = '4'";
        int i = statement.executeUpdate(sql);
        System.out.println(i);
        //-------------------
//        int j = statement.executeUpdate("insert into user values(null,'丁七','0')");
//        System.out.println(j);
        //-------------------
        ResultSet resultSet = statement.executeQuery("select * from  user");
        while (resultSet.next()) {
            System.out.print(resultSet.getInt(1));
            System.out.print(resultSet.getString("name"));
            System.out.println(resultSet.getString(3));

        }


        resultSet.close();
        statement.close();
        connect.close();
    }


}
